﻿using System;
using System.Data.SqlClient;
using System.Data;

namespace Epicor.Pub
{
    /// <summary>SqlDB操作库</summary>
    public class SqlDB
    {
        /// <summary>连接字符串</summary>
        public string ConnStr = "";
        /// <summary>连接超时时长</summary>
        public int CommTimeOut = 30;
        /// <summary>SqlDB操作库构造函数</summary>
        public SqlDB()
        {

        }
        /// <summary>SqlDB操作库构造函数</summary>
        public SqlDB(string connStr)
        {
            ConnStr = connStr;
        }

        #region 执行sql字符串
        /// <summary> 执行SQL语句,返回第一行第一列的值 </summary>
        public object ExecuteScalar(String Sqlstr, SqlParameter[] param = null)
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandTimeout = CommTimeOut;
                cmd.CommandText = Sqlstr;
                if (param != null) cmd.Parameters.AddRange(param);
                conn.Open();
                object obj = cmd.ExecuteScalar();
                conn.Close();
                return obj;
            }
        }

        /// <summary> 执行SQL语句,返回第影响行数 </summary>
        public int ExecuteSql(String Sqlstr, SqlParameter[] param=null)
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandTimeout = CommTimeOut;
                cmd.CommandText = Sqlstr;
                if (param != null) cmd.Parameters.AddRange(param);
                conn.Open();
                int i=cmd.ExecuteNonQuery();
                conn.Close();
                return i;
            }
        }

        /// <summary>执行多条SQL语句，实现数据库事务。 </summary>
        public  void ExecuteSqlTran(KeyValue[] SQLStrArray)
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        for (int i = 0; i < SQLStrArray.Length; i++)
                        {
                            if (SQLStrArray[i] != null && SQLStrArray[i].Key!="")
                            {
                                string cmdText = SQLStrArray[i].Key;
                                SqlParameter[] cmdParms;
                                if (SQLStrArray[i].Value == null) cmdParms = null;
                                else cmdParms = (SqlParameter[])SQLStrArray[i].Value;
                                if (conn.State != ConnectionState.Open)conn.Open();
                                cmd.Connection = conn;
                                cmd.CommandTimeout = CommTimeOut;
                                cmd.CommandText = cmdText;
                                if (trans != null)
                                    cmd.Transaction = trans;
                                cmd.CommandType = CommandType.Text;//cmdType;
                                if (cmdParms != null)
                                {
                                    foreach (SqlParameter parm in cmdParms)
                                        cmd.Parameters.Add(parm);
                                }
                                int val = cmd.ExecuteNonQuery();
                                cmd.Parameters.Clear();
                            }
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }

        /// <summary>执行SQL语句并返回DataTable数据表 </summary>
        public DataTable ExecuteDt(String Sqlstr, SqlParameter[] param = null)
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandTimeout = CommTimeOut;
                cmd.CommandText = Sqlstr;
                if (param != null) cmd.Parameters.AddRange(param);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                conn.Open();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
        }
        /// <summary> 执行SQL语句并返回DataSet数据集 </summary>
        public DataSet ExecuteDs(String Sqlstr, SqlParameter[] param = null)
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandTimeout = CommTimeOut;
                cmd.CommandText = Sqlstr;
                if (param != null) cmd.Parameters.AddRange(param);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                conn.Open();
                da.Fill(ds);
                conn.Close();
                return ds;
            }
        }
        #endregion
        #region 操作存储过程
        /// <summary> 运行存储过程 </summary>
        public int RunProc(string procName, SqlParameter[] param = null)
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand(procName, conn);
                cmd.CommandTimeout = CommTimeOut;
                cmd.CommandType = CommandType.StoredProcedure;
                if (param != null) cmd.Parameters.AddRange(param);
                conn.Open();
                int i=cmd.ExecuteNonQuery();
                conn.Close();
                return i;
            }
        }
        ///<summary>运行存储过程,返回DataTable结果表</summary>
        public DataTable RunProcDT(string procName, SqlParameter[] param = null)
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand(procName, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = CommTimeOut;
                if (param != null) cmd.Parameters.AddRange(param);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                conn.Open();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
        }
        ///<summary>运行存储过程,返回DataSet结果表</summary>
        public DataSet RunProcDS(string procName, SqlParameter[] param = null)
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand(procName, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = CommTimeOut;
                if (param != null) cmd.Parameters.AddRange(param);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                conn.Open();
                da.Fill(ds);
                conn.Close();
                return ds;
            }
        }
        #endregion


    }
    /// <summary>KeyValue类</summary>
    public class KeyValue
    {
        /// <summary>Key值</summary>
        public string Key { get; set; }
        /// <summary>Value值</summary>
        public object Value { get; set; }
        /// <summary>KeyValue类构造函数</summary>
        public KeyValue(string key, object value = null)
        {
            Key = key;
            Value = value;
        }
        /// <summary>重新ToString函数</summary>
        public override string ToString()
        {
            return Key;
        }
    }
}
